Chapter 9\Employees.asp

<%@ Language=VBScript %>
<?xml version="1.0" standalone="yes"?>
<!DOCTYPE employees [
    <!ELEMENT employees ( employee+ )>

    <!ELEMENT employee (name, position, address, phone)>
    <!ATTLIST employee id ID #REQUIRED>

    <!ELEMENT name (first, last)>
    <!ELEMENT first (#PCDATA)>
    <!ELEMENT last  (#PCDATA)>

    <!ELEMENT position (#PCDATA)>

    <!ELEMENT address (street?, city?, state?, zip?)>
    <!ELEMENT street (#PCDATA)>
    <!ELEMENT city   (#PCDATA)>
    <!ELEMENT state  (#PCDATA)>
    <!ELEMENT zip    (#PCDATA)>

    <!ELEMENT phone (main, office*, fax*, mobile*, home*)>
    <!ELEMENT main   (#PCDATA)>
    <!ELEMENT office (#PCDATA)>
    <!ELEMENT fax    (#PCDATA)>
    <!ELEMENT mobile (#PCDATA)>
    <!ELEMENT home   (#PCDATA)>
]>
<employees>
<%
    Dim oConn    ' ADO connection object.
    Dim oRs        ' Employee recordset object.
    Dim oRsPhone    ' Emp phone recordset object.
    Dim strSql    ' SQL string

    ' Establish a connection to the database.    
    Set oConn = Server.CreateObject("ADODB.Connection")
    Call oConn.Open( "DSN=EmployeesMDB;UID=Admin;PWD=" )
    
    ' Open a recordset containing all of the employees.
    strSql = "SELECT * FROM employees ORDER BY lastname, firstname"
    Set oRs = Server.CreateObject( "ADODB.Recordset" )
    Call oRs.Open( strSql, oConn, adOpenStatic )

    ' Iterate through all of the employees in
    ' the database and generate the appropriate
    ' XML document that contains their information.    
    While Not oRs.EOF

        ' Display all of the employee information
        ' that is in the employee table.
        Response.Write( "<employee id=""" & oRs("id") & """>" & vbCrLf )
        Response.Write( "    <name>" & vbCrLf )
        Response.Write( "        <first>" & oRs("firstname") & "</first>" & vbCrLf )
        Response.Write( "        <last>" & oRs("lastname") & "</last>" & vbCrLf )
        Response.Write( "    </name>" & vbCrLf )
        Response.Write( "    <position>" & oRs("position") & "</position>" & vbCrLf )
        Response.Write( "    <address>" & vbCrLf )
        Response.Write( "        <street>" & oRs("street") & "</street>" & vbCrLf )
        Response.Write( "        <city>" & oRs("city") & "</city>" & vbCrLf )
        Response.Write( "        <state>" & oRs("state") & "</state>" & vbCrLf )
        Response.Write( "        <zip>" & oRs("zip") & "</zip>" & vbCrLf )
        Response.Write( "    </address>" & vbCrLf )
        Response.Write( "    <phone>" & vbCrLf )
        
        ' Get the phone numbers for this employee
        ' and generate the appropriate tags and content.
        Set oRsPhones = Server.CreateObject( "ADODB.Recordset" )
        Call oRsPhones.Open( "SELECT * FROM phonenumbers WHERE id = '" & oRs("id") & "'", oConn )
    
        While Not oRsPhones.EOF
            ' Generate the tag containing the appropriate
            ' phone information (type and number)
            Response.Write( "        <" & oRsPhones("phonetype") & ">" )
            Response.Write( oRsPhones("phonenumber") )
            Response.Write( "</" & oRsPhones("phonetype") & ">" & vbCrLf )
            oRsPhones.MoveNext
        Wend
        oRsPhones.Close
        
        Response.Write( "    </phone>" & vbCrLf )
        Response.Write( "</employee>" & vbCrLf & vbCrLf )
        
        oRs.MoveNext 
    Wend
    
    oRs.Close
    oConn.Close 
%>
</employees>